﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using TugasAkhir.Helpers;
using TugasAkhir.Objects;

namespace TugasAkhir.Models
{
    public static class DetailPertanyaanModel
    {
        public static bool TambahPertanyaan(string idPertanyaan, string tahun, string periode)
        {
            var dataPengambilan = new DataSet();
            bool pengambilanFlag = PengambilanMahasiswaModel.GetPengambilanMatakuliah(periode, tahun, out dataPengambilan);

            bool flag = true;

            if (pengambilanFlag)
            {
                if (dataPengambilan.Tables[0] != null && dataPengambilan.Tables[0].Rows.Count > 0)
                {
                    foreach (DataRow item in dataPengambilan.Tables[0].Rows)
                    {
                        var newDP = new DetailPertanyaanObject();
                        newDP.Kelas = item[0].ToString();
                        newDP.Periode = item[1].ToString();
                        newDP.Tahun = item[2].ToString();
                        newDP.IdMatakuliah = item[3].ToString();
                        newDP.IdMahasiswa = item[4].ToString();
                        newDP.IdPertanyaan = idPertanyaan;
                        newDP.KodeJawaban = "0";
                        newDP.StatusPengisian = "0";

                        string query = String.Format("INSERT INTO [dbo].[detail_pertanyaan] VALUES ('{0}' ,'{1}' ,'{2}' ,'{3}' ,'{4}' ,'{5}' ,'{6}' ,'{7}')", newDP.Kelas, newDP.Periode, newDP.Tahun, newDP.IdMatakuliah, newDP.IdMahasiswa, newDP.IdPertanyaan, newDP.KodeJawaban, newDP.StatusPengisian);

                        flag = SqlServer.ExecuteNonQuery(query);
                        if (!flag) break;
                    }
                    return flag;
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        public static bool GetSemuaDetailPertanyaan(string periode, string tahun, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT DP.[id_pertanyaan], CONVERT(VARCHAR(MAX), P.[pertanyaan]) AS pertanyaan FROM [dbo].[detail_pertanyaan] DP, [dbo].[pertanyaan] P WHERE DP.[id_pertanyaan] = P.[id_pertanyaan] AND DP.[periode] = '{0}' AND DP.[tahun] = '{1}'", periode, tahun);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool HapusDetailPertanyaan(string periode, string tahun, string idPertanyaan)
        {
            string query = String.Format("DELETE FROM [dbo].[detail_pertanyaan] WHERE [periode] = '{0}' AND [tahun] = '{1}' AND [id_pertanyaan] = '{2}'", periode, tahun, idPertanyaan);
            return SqlServer.ExecuteNonQuery(query);
        }

        public static bool GetListMahasiswa(string idMahasiswa, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT MT.[id_matakuliah], M.[id_mahasiswa], MT.[nama_matakuliah], DP.[tahun], DP.[periode], DP.[kelas], DP.[status_pengisian], D.[nama_dosen] FROM [dbo].[detail_pertanyaan] DP, [dbo].[matakuliah] MT, [dbo].[mahasiswa] M, [dbo].[dosen] D, [dbo].[pengambilan_dosen] PD, [dbo].[pelaksanaan_matakuliah] PM WHERE DP.[id_matakuliah] = MT.[id_matakuliah] AND DP.[id_mahasiswa] = M.[id_mahasiswa] AND PD.[id_dosen] = D.[id_dosen] AND PD.[id_matakuliah] = MT.[id_matakuliah] AND PD.[periode] = PM.[periode] AND PD.[tahun] = PM.[tahun] AND PD.[kelas] = PM.[kelas] AND DP.[status_pengisian] = '0' AND M.[id_mahasiswa] = '{0}'", idMahasiswa);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetJawaban(out DataSet hasilQuery)
        {
            string query = "SELECT * FROM [dbo].[jawaban] WHERE [kode_jawaban] != 0";
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetPertanyaanKuisioner(string idMahasiswa, string kelas, string tahun, string idMatakuliah, string periode, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DP.[id_pertanyaan], P.[pertanyaan] FROM [dbo].[detail_pertanyaan] DP, [dbo].[pertanyaan] P WHERE DP.[id_pertanyaan] = P.[id_pertanyaan] AND DP.[id_mahasiswa] = '{0}' AND DP.[kelas] = '{1}' AND DP.[tahun] = '{2}' AND DP.[id_matakuliah] = '{3}' AND DP.[periode] = '{4}'", idMahasiswa, kelas, tahun, idMatakuliah, periode);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool Update(string idMahasiswa, string kelas, string tahun, string idMatakuliah, string periode, string kode, string idPertanyaan)
        {
            string query = String.Format("UPDATE [dbo].[detail_pertanyaan] SET [kode_jawaban] = '{0}' ,[status_pengisian] = '1' WHERE [kelas] = '{1}' AND [periode] = '{2}' AND [tahun] = '{3}' AND [id_matakuliah] = '{4}' AND [id_mahasiswa] = '{5}' AND [id_pertanyaan] = '{6}'", kode, kelas, periode, tahun, idMatakuliah, idMahasiswa, idPertanyaan);
            return SqlServer.ExecuteNonQuery(query);
        }

        public static bool GetMatakuliah(string tahun, string periode, string idLab, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT DP.[id_matakuliah], M.[nama_matakuliah] FROM [dbo].[detail_pertanyaan] DP, [dbo].[matakuliah] M WHERE DP.[id_matakuliah] = M.[id_matakuliah] AND DP.[tahun] = '{0}' AND DP.[periode] = '{1}' AND M.[id_laboratorium] = '{2}'", tahun, periode, idLab);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }

        public static bool GetPertanyaan(string tahun, string periode, string idMatakuliah, out DataSet hasilQuery)
        {
            string query = String.Format("SELECT DISTINCT DP.[id_pertanyaan], CONVERT(VARCHAR(MAX), P.[pertanyaan]) AS [pertanyaan] FROM [dbo].[detail_pertanyaan] DP, [dbo].[pertanyaan] P WHERE DP.[id_pertanyaan] = P.[id_pertanyaan] AND DP.[tahun] = '{0}' AND DP.[periode] = '{1}' AND DP.[id_matakuliah] = '{2}'", tahun, periode, idMatakuliah);
            return SqlServer.ExecuteReader(query, out hasilQuery);
        }
    }
}